package com.example.dao;

import com.example.domain.Cart;
import com.example.domain.CartWithProduct;
import com.example.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class CartDao {
    /**
     * 判断该商品是否在购物车列表中
     */
    public boolean isProductInCart(int memberId, int productId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM tb_cart WHERE member_id = ? AND product_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, memberId);
            stmt.setInt(2, productId);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeAll(rs, stmt, conn);
        }
    }
    
    /**
     * 添加商品到购物车列表中
     */
    public void addProductToCart(Cart cart) throws SQLException {
        if (cart == null) {
            throw new IllegalArgumentException("购物车信息不能为空");
        }
        
        String sql = "INSERT INTO tb_cart (member_id, product_id, sale_num, sale_price, sale_money) VALUES (?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, cart.getMember_id());
            stmt.setInt(2, cart.getProduct_id());
            stmt.setInt(3, cart.getSale_num());
            stmt.setDouble(4, cart.getSale_price());
            stmt.setDouble(5, cart.getSale_money());
            stmt.executeUpdate();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }
    
    /**
     * 更新购物车
     */
    public void updateCart(Cart cart) throws SQLException {
        if (cart == null || cart.getId() <= 0) {
            throw new IllegalArgumentException("无效的购物车ID");
        }
        
        String sql = "UPDATE tb_cart SET member_id = ?, product_id = ?, sale_num = ?, sale_price = ?, sale_money = ? WHERE id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, cart.getMember_id());
            stmt.setInt(2, cart.getProduct_id());
            stmt.setInt(3, cart.getSale_num());
            stmt.setDouble(4, cart.getSale_price());
            stmt.setDouble(5, cart.getSale_money());
            stmt.setInt(6, cart.getId());
            stmt.executeUpdate();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }
    
    /**
     * 更新购物车商品数量
     */
    public void updateCartQuantity(int memberId, int productId, int quantity) throws SQLException {
        String sql = "UPDATE tb_cart SET sale_num = ?, updated_at = CURRENT_TIMESTAMP WHERE member_id = ? AND product_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, quantity);
            stmt.setInt(2, memberId);
            stmt.setInt(3, productId);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }
    
    /**
     * 更新购物车商品数量和价格
     */
    public void updateCartQuantityAndPrice(int memberId, int productId, int quantity, double price) throws SQLException {
        String sql = "UPDATE tb_cart SET sale_num = ?, sale_price = ?, sale_money = ?, updated_at = CURRENT_TIMESTAMP WHERE member_id = ? AND product_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, quantity);
            stmt.setDouble(2, price);
            stmt.setDouble(3, price * quantity);
            stmt.setInt(4, memberId);
            stmt.setInt(5, productId);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }
    
    /**
     * 显示购物车列表
     */
    public List<CartWithProduct> getCartListByMemberId(int memberId) throws SQLException {
        List<CartWithProduct> cartList = new ArrayList<>();
        String sql = "SELECT c.id, c.member_id, c.product_id, p.product_name, c.sale_num, c.sale_price, c.sale_money, c.created_at, c.updated_at " +
                     "FROM tb_cart c " +
                     "JOIN tb_product p ON c.product_id = p.id " +
                     "WHERE c.member_id = ?";
                     
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, memberId);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                CartWithProduct cartItem = new CartWithProduct();
                cartItem.setId(rs.getInt("id"));
                cartItem.setMember_id(rs.getInt("member_id"));
                cartItem.setProduct_id(rs.getInt("product_id"));
                cartItem.setProduct_name(rs.getString("product_name"));
                cartItem.setSale_num(rs.getInt("sale_num"));
                cartItem.setSale_price(rs.getDouble("sale_price"));
                cartItem.setSale_money(rs.getDouble("sale_money"));
                cartItem.setCreated_at(rs.getTimestamp("created_at"));
                cartItem.setUpdated_at(rs.getTimestamp("updated_at"));
                cartList.add(cartItem);
            }
        } finally {
            DBUtil.closeAll(rs, stmt, conn);
        }
        return cartList;
    }
    
    /**
     * 计算购物车中的商品总数和总金额
     */
    public Optional<Cart> calculateCartSummary(int memberId) throws SQLException {
        String sql = "SELECT SUM(sale_num) as total_num, SUM(sale_money) as total_money FROM tb_cart WHERE member_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, memberId);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                Cart summary = new Cart();
                summary.setSale_num(rs.getInt("total_num"));
                summary.setSale_money(rs.getDouble("total_money"));
                return Optional.of(summary);
            }
        } finally {
            DBUtil.closeAll(rs, stmt, conn);
        }
        return Optional.empty();
    }
    
    /**
     * 根据会员ID和产品ID获取购物车项目
     */
    public Optional<Cart> getCartByMemberAndProduct(int memberId, int productId) throws SQLException {
        String sql = "SELECT * FROM tb_cart WHERE member_id = ? AND product_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, memberId);
            stmt.setInt(2, productId);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                Cart cart = new Cart();
                cart.setId(rs.getInt("id"));
                cart.setMember_id(rs.getInt("member_id"));
                cart.setProduct_id(rs.getInt("product_id"));
                cart.setSale_num(rs.getInt("sale_num"));
                cart.setSale_price(rs.getDouble("sale_price"));
                cart.setSale_money(rs.getDouble("sale_money"));
                cart.setCreated_at(rs.getTimestamp("created_at"));
                cart.setUpdated_at(rs.getTimestamp("updated_at"));
                return Optional.of(cart);
            }
        } finally {
            DBUtil.closeAll(rs, stmt, conn);
        }
        return Optional.empty();
    }
    
    /**
     * 从购物车中删除商品
     */
    public void removeProductFromCart(int memberId, int productId) throws SQLException {
        String sql = "DELETE FROM tb_cart WHERE member_id = ? AND product_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, memberId);
            stmt.setInt(2, productId);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }
    
    /**
     * 清空购物车
     */
    public void clearCart(int memberId) throws SQLException {
        String sql = "DELETE FROM tb_cart WHERE member_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, memberId);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }
}